package dartstools;

import java.sql.*;

/**
 *
 * @author Chris
 */
public class Database {

    private static Connection conn;
    private static Statement stmt;
    private static PreparedStatement prepGeneral;
    private static ResultSet rs;

    private Database() {
    }

    static {
        try {
            Class.forName("org.h2.Driver");
            conn = DriverManager.getConnection("jdbc:h2:~/.darts/.darts-data");
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            dropTables(34687);
            createTables();
        }
        catch (ClassNotFoundException | SQLException e) {
            System.err.println(e.getMessage());
        }
    }

    private static void createTables() {
        try {
            stmt.executeUpdate("CREATE TABLE players("
                    + "playerid SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL," // SMALLINT = 32767 max
                    + "name CHAR(20) NOT NULL UNIQUE,"
                    + "added TIMESTAMP AS NOW(0) NOT NULL);");
            stmt.executeUpdate("CREATE TABLE legs("
                    + "legid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,"
                    + "target_score SMALLINT UNSIGNED NOT NULL,"
                    + "added TIMESTAMP AS NOW(0) NOT NULL,"
                    + "CHECK (target_score > 0));");
            stmt.executeUpdate("CREATE TABLE leg_players("
                    + "legid INT REFERENCES legs(legid),"
                    + "playerid SMALLINT REFERENCES players(playerid),"
                    + "UNIQUE(legid, playerid));");
            stmt.executeUpdate("CREATE TABLE leg_throws("
                    + "legid INT REFERENCES legs(legid),"
                    + "playerid SMALLINT REFERENCES players(playerid),"
                    + "dartgroupno TINYINT NOT NULL," // TINYINT 127 max
                    + "dart1 TINYINT,"
                    + "dart2 TINYINT,"
                    + "dart3 TINYINT,"
                    + "dart1_type char(1) DEFAULT 's' NOT NULL," //    single = s    double = d    treble = t
                    + "dart2_type char(1) DEFAULT 's' NOT NULL,"
                    + "dart3_type char(1) DEFAULT 's' NOT NULL,"
                    + "added TIMESTAMP AS NOW(0) NOT NULL,"
                    + "UNIQUE(legid, playerid, dartgroupno),"
                    + "CHECK (dart1 < 60 AND dart2 < 60 AND dart3 < 60) AND"
                    + "(dart1_type = 's' OR dart1_type = 'd' OR dart1_type = 't') AND"
                    + "(dart2_type = 's' OR dart2_type = 'd' OR dart2_type = 't') AND"
                    + "(dart3_type = 's' OR dart3_type = 'd' OR dart3_type = 't'));");
        }
        catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }

    private static void dropTables(int dropKey) {
        if (dropKey == 34687) {
            try {
                stmt.executeUpdate("DROP TABLE players;");
                stmt.executeUpdate("DROP TABLE legs;");
                stmt.executeUpdate("DROP TABLE leg_players;");
                stmt.executeUpdate("DROP TABLE leg_throws;");
            }
            catch (SQLException e) {
                System.err.println(e.getMessage());
            }
        }
    }

    public static void addUser(String name) {
        try {
            prepGeneral = conn.prepareStatement("INSERT INTO players(name) VALUES(?);");
            prepGeneral.setString(1, name);
            prepGeneral.executeUpdate();
            conn.commit();
        }
        catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }

    public static void addLeg(int soloPlayerId, int targetScore) {
        addLeg(soloPlayerId, -1, -1, -1, targetScore);
    }

    public static void addLeg(int playerid1, int playerid2, int targetScore) {
        addLeg(playerid1, playerid2, -1, -1, targetScore);
    }

    public static void addLeg(int playerid1, int playerid2, int playerid3, int targetScore) {
        addLeg(playerid1, playerid2, playerid3, -1, targetScore);
    }

    public static void addLeg(int playerid1, int playerid2, int playerid3, int playerid4, int targetScore) {
            try {
                prepGeneral = conn.prepareStatement("INSERT INTO legs(target_score) VALUES(?);");
                prepGeneral.setInt(1, targetScore);
                prepGeneral.executeUpdate();
                rs = stmt.executeQuery("SELECT MAX(legid) AS \"last_legid\" FROM legs;");
                rs.next();
                int legId = rs.getInt("last_legid");
                prepGeneral = conn.prepareStatement("INSERT INTO leg_players VALUES(?,?);");
                prepGeneral.setInt(1, legId);
                prepGeneral.setInt(2, playerid1);
                prepGeneral.addBatch();
                if (playerid2 != -1) {
                    prepGeneral.setInt(2, playerid2);
                    prepGeneral.addBatch();
                }
                if (playerid3 != -1) {
                    prepGeneral.setInt(2, playerid3);
                    prepGeneral.addBatch();
                }
                if (playerid4 != -1) {
                    prepGeneral.setInt(2, playerid4);
                    prepGeneral.addBatch();
                }
                prepGeneral.executeBatch();
                conn.commit();
            }
            catch (SQLException e) {
                System.err.println(e.getMessage());
            }
    }

    public static void addThrow(int legid, int playerid,
            int dart1, char dart1Type,
            int dart2, char dart2Type,
            int dart3, char dart3Type) {
        if ((dart1 + dart2 + dart3) > 180)
            throw new IllegalArgumentException("Darts total cannot be > 180 (" + dart1 + "," + dart2 + "," + dart3 + ")");
        try {
            prepGeneral = conn.prepareStatement("SELECT MAX(dartgroupno) AS \"last_group_no\" FROM leg_throws WHERE legid = ? AND playerid = ?;");
            prepGeneral.setInt(1, legid);
            prepGeneral.setInt(2, playerid);
            rs = prepGeneral.executeQuery();
            int dartGroupNo = 0;
            if (rs.next())
                dartGroupNo = rs.getInt("last_group_no") + 1;
            prepGeneral = conn.prepareStatement("INSERT INTO leg_throws(legid,playerid,dartgroupno,dart1,dart2,dart3,dart1_type,dart2_type,dart3_type) VALUES(?,?,?,?,?,?,?,?,?);");
            prepGeneral.setInt(1, legid);
            prepGeneral.setInt(2, playerid);
            prepGeneral.setInt(3, dartGroupNo);
            prepGeneral.setInt(4, dart1);
            prepGeneral.setInt(5, dart2);
            prepGeneral.setInt(6, dart3);
            prepGeneral.setString(7, String.valueOf(dart1Type));
            prepGeneral.setString(8, String.valueOf(dart2Type));
            prepGeneral.setString(9, String.valueOf(dart3Type));
            prepGeneral.executeUpdate();
            conn.commit();
        }
        catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }

//    private static void printPlayersTable() {
//        try {
//            try (ResultSet rs = stmt.executeQuery("SELECT * FROM players;")) {
//                System.out.println("playerid" + "\tname" + "\t\tadded");
//                while (rs.next())
//                    System.out.println(rs.getInt("playerid") + "\t\t" + rs.getString("name") + "\t\t" + rs.getString("added"));
//            }
//        }
//        catch (SQLException e) {
//            System.err.println(e.getMessage());
//        }
//    }
//
//    private static void printLegsTable() {
//        try {
//            try (ResultSet rs = stmt.executeQuery("SELECT * FROM legs;")) {
//                System.out.println("legid" + "\tadded");
//                while (rs.next())
//                    System.out.println(rs.getInt("legid") + "\t" + rs.getString("added"));
//            }
//        }
//        catch (SQLException e) {
//            System.err.println(e.getMessage());
//        }
//    }
//
//    private static void printLegPlayersTable() {
//        try {
//            try (ResultSet rs = stmt.executeQuery("SELECT * FROM leg_players;")) {
//                System.out.println("legid" + "\tplayerid");
//                while (rs.next())
//                    System.out.println(rs.getInt("legid") + "\t" + rs.getInt("playerid"));
//            }
//        }
//        catch (SQLException e) {
//            System.err.println(e.getMessage());
//        }
//    }
    private static void printTable(String table) {
        try {
            ResultSet rs = stmt.executeQuery("SELECT * FROM " + table + ";");
            ResultSetMetaData rsMetaData = rs.getMetaData();
            int numberOfColumns = rsMetaData.getColumnCount();
            for (int i = 0; i < numberOfColumns; i++) {
                String label = rsMetaData.getColumnLabel(i + 1);
                System.out.print(label + "\t");
                if (label.length() < 8)
                    System.out.print("\t");
            }
            System.out.println();
            while (rs.next()) {
                for (int j = 0; j < numberOfColumns; j++) {
                    System.out.print(rs.getString(j + 1) + "\t\t");
                }
                System.out.println();
            }
        }
        catch (SQLException ex) {
            System.err.println(ex.getMessage());
        }
    }

    public static void main(String[] args) {
        try {
            addUser("Chris");
            addUser("Dad");
            addUser("Claire");
            addUser("Mum");
            printTable("players");
            System.out.println();

            addLeg(1, 2, 3, 4, 301);
            printTable("legs");
            System.out.println();

            printTable("leg_players");
            System.out.println();

            addThrow(1, 3, 34, 'd', 5, 's', 33, 't');
            printTable("leg_throws");
            conn.close();
        }
        catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }

}